﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
namespace DbUtility
{
    public class AccessOperation
    {
        private readonly string _path;

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private readonly string _connectionString;

        /// <summary>
        /// 数据库连接
        /// </summary>
        private readonly OleDbConnection _connection;

        public AccessOperation(string path)
        {
            _path = path;
            _connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Persist Security Info=False";
            _connection = new OleDbConnection(_connectionString);
        }

        public string ConnectionString
        {
            get
            {
                return _connectionString;
            }
        }

        /// <summary>
        /// 执行SQL语句没有返回结果，如：执行删除、更新、插入等操作
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>操作成功标志</returns>
        public bool ExecuteNonQuery(string strSQL)
        {
            bool resultState = false;

            _connection.Open();
            OleDbTransaction myTrans = _connection.BeginTransaction();
            OleDbCommand command = new OleDbCommand(strSQL, _connection, myTrans);

            try
            {
                command.ExecuteNonQuery();
                myTrans.Commit();
                resultState = true;
            }
            catch (Exception)
            {
                myTrans.Rollback();
                resultState = false;
            }
            finally
            {
                myTrans.Dispose();
                command.Dispose();
                _connection.Close();
            }
            return resultState;
        }

        /// <summary>
        /// 执行SQL语句返回结果到DataReader中
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>dataReader</returns>
        public OleDbDataReader ExecuteDataReader(string strSQL)
        {
            if (_connection != null)
            {
                _connection.Open();
                OleDbCommand command = new OleDbCommand(strSQL, _connection);
                OleDbDataReader dataReader = command.ExecuteReader();
                _connection.Close();
                command.Dispose();
                return dataReader;
            }
            return null;
        }

        /// <summary>
        /// 执行SQL语句返回结果到DataSet中
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string strSql)
        {
            if (_connection != null)
            {
                _connection.Open();
                DataSet dataSet = new DataSet();
                OleDbDataAdapter oleDbDa = new OleDbDataAdapter(strSql, _connection);
                oleDbDa.Fill(dataSet, "myDataSet");

                _connection.Close();
                oleDbDa.Dispose();
                return dataSet;
            }
            return null;
        }

        /// <summary>
        /// 执行SQL语句返回结果到DataTable中
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string strSql)
        {
            return ExecuteDataSet(strSql).Tables[0];
        }

        /// <summary>
        /// 获得当前数据库的所有表名
        /// </summary>
        /// <returns>表名</returns>
        public List<string> GetTableNameList()
        {
            List<string> list = new List<string>();
            try
            {
                if (_connection.State == ConnectionState.Closed)
                    _connection.Open();
                DataTable dt = _connection.GetSchema("Tables");
                foreach (DataRow row in dt.Rows)
                {
                    if (row[3].ToString() == "TABLE")
                        list.Add(row[2].ToString());
                }
                return list;
            }
            finally 
            {
                if (_connection.State == ConnectionState.Open) 
                    _connection.Close();

            }
        }

        /// <summary>
        /// 获得当前数据库的所有列
        /// </summary>
        /// <returns>列名</returns>
        public List<string> GetTableFieldNameList(string tableName)
        {
            List<string> list = new List<string>();
            try
            {
                if (_connection.State == ConnectionState.Closed)
                    _connection.Open();
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.CommandText = "SELECT TOP 1 * FROM [" + tableName + "]";
                    cmd.Connection = _connection;
                    OleDbDataReader dr = cmd.ExecuteReader();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        list.Add(dr.GetName(i));
                    }
                }
                return list;
            }
            finally
            {
                if (_connection.State == ConnectionState.Open)
                    _connection.Close();
            }
        }

    }
}
